package com.ccsoft.gsms.utils;

import com.ccsoft.gsms.model.DBConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName:DynamicDBUtils
 * @Description:TODO
 * @Author:chanchaw
 * @Date:2019/11/14 13:16
 * @Version:1.0
 **/
@Service
public class DynamicDBUtils {
    /**
     * 查询返回记录集
     * @param dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }
     * @param sql 要执行的SQL语句字串，已经将参数拼接在内了，不用传查询参数进来
     * @return
     */
    public List<Map<String,Object>> queryForList(DBConfig dbConfig, String sql){
        HikariDataSource dataSource = customDataSource(dbConfig);
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new HashMap<>());

        dataSource.close();
        return list;
    }


    /**
     * 查询返回记录集
     * @param dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }
     * @param sql 查询SQL语句，带有占位符，例如：SELECT * FROM projects WHERE 1=1 AND iid>:iid; 最后的 :iid 是参数占位符
     * @param params Map<String,Object> 类型的参数，其中包含1 - N 个参数，键名称要和第二个参数的占位符保持一致
     * @return
     */
    public List<Map<String,Object>> queryForList(DBConfig dbConfig,String sql,Map<String,Object> params){
        HikariDataSource dataSource = customDataSource(dbConfig);
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new MapSqlParameterSource(params));

        dataSource.close();
        return list;
    }

    /**
     * 增/删/改 数据
     * @param dbConfig
     * @param sql
     * @return
     */
    public int update(DBConfig dbConfig,String sql){
        HikariDataSource dataSource = customDataSource(dbConfig);
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        int ret = jdbcTemplate.update(sql,new HashMap<>());
        dataSource.close();
        return ret;
    }


    /**
     * 增/删/改 数据
     * @param dbConfig dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }
     * @param sql 查询SQL语句，带有占位符，例如：SELECT * FROM projects WHERE 1=1 AND iid>:iid; 最后的 :iid 是参数占位符
     * @param params Map<String,Object> 类型的参数，其中包含1 - N 个参数，键名称要和第二个参数的占位符保持一致
     * @return
     */
    public int update(DBConfig dbConfig,String sql,Map<String,Object> params){
        HikariDataSource dataSource = customDataSource(dbConfig);
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        int ret = jdbcTemplate.update(sql,new MapSqlParameterSource(params));
        dataSource.close();
        return ret;
    }



    public HikariDataSource customDataSource(DBConfig dbConfig) {

        final HikariDataSource customDataConfig = new HikariDataSource();

        customDataConfig.setJdbcUrl(dbConfig.getUrl());

        // 不设置驱动 - 采用默认驱动
        if( dbConfig.getDriver() == null||dbConfig.getDriver().length()<=0 ){
            customDataConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
        }else{
            customDataConfig.setDriverClassName(dbConfig.getDriver());
        }



        customDataConfig.setUsername(dbConfig.getDbUserName());
        customDataConfig.setPassword(dbConfig.getDbUserPwd());

        customDataConfig.setMaximumPoolSize(2);
        customDataConfig.setMinimumIdle(1);
        customDataConfig.setAutoCommit(true);
        //customDataConfig.setConnectionTestQuery("SELECT 1 FROM DUAL");
        customDataConfig.addDataSourceProperty("cachePrepStmts", "true");
        customDataConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        customDataConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        return customDataConfig;
    }
}
